﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LogisticsModel;

namespace LogisticsDAL
{
    public class TruckDAL
    {

        #region 车辆列表
        /// <summary>
        /// 车辆列表
        /// </summary>
        /// <returns></returns>
        public static List<Truck> GetTruckList(int teamId,string number,string type,int state)
        {
            //string sql = "SELECT * FROM Truck,TruckTeam WHERE Truck.FK_TeamID=TruckTeam.TeamID";
            StringBuilder builder = new StringBuilder("SELECT * FROM Truck,TruckTeam WHERE Truck.FK_TeamID=TruckTeam.TeamID");

            List<Truck> trucks = new List<Truck>();
            List<SqlParameter> parameters = new List<SqlParameter>();
            List<string> andList = new List<string>();
            if (teamId>0)
            {
                andList.Add("FK_TeamID=@teamId");
                parameters.Add(new SqlParameter("@teamId", teamId));
            }
            if (!string.IsNullOrWhiteSpace(number))
            {
                andList.Add("Number=@number");
                parameters.Add(new SqlParameter("@number", number));

            }
            if (type!="0" && type!=null)
            {
                andList.Add("Type=@type");
                parameters.Add(new SqlParameter("@type", type));
            }
            if (state!=0)
            {
                andList.Add("State=@state");
                parameters.Add(new SqlParameter("@state", state));
            }
            if (andList.Count>0)
            {
                //string str= string.Join("and", andList.ToArray());
                builder.Append($" and {string.Join(" and ", andList.ToArray())}");

                
            }
       

            SqlDataReader reader=  SqlHelper.ExecuteReader(builder.ToString(), parameters.ToArray());
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Truck truck = new Truck
                    {
                        Remark = reader["Remark"].ToString(),
                        TruckID = Convert.ToInt32(reader["TruckID"]),
                        Type = reader["Type"].ToString(),
                        Tonnage = Convert.ToInt32(reader["Tonnage"]),
                        State = Convert.ToByte(reader["State"]),
                        Number = reader["Number"].ToString(),
                        FK_TeamID=Convert.ToInt32(reader["FK_TeamID"]),
                        BuyDate = Convert.ToDateTime(reader["BuyDate"]),
                        TeamName = reader["TeamName"].ToString(),
                        CheckInTime = Convert.ToDateTime(reader["CheckInTime"]),
                        IsDelete = Convert.ToByte(reader["State"]),
                        AlterTime = Convert.ToDateTime(reader["AlterTime"]),
                        Length = reader["Length"].ToString(),
                        
                    };
                    trucks.Add(truck);

                }
            }
            reader.Close();
            return trucks;

        }
        #endregion

        #region 车辆详情
        public static Truck GetTruck(string tid)
        {
            string sql = "SELECT * FROM Truck,TruckTeam WHERE Truck.FK_TeamID=TruckTeam.TeamID AND Truck.truckID=@tid";
            SqlParameter[] parameters = {
            new SqlParameter("@tid",tid)
            };
            SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameters);
            Truck truck = null;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    truck = new Truck
                    {
                        Remark = reader["Remark"].ToString(),
                        TruckID = Convert.ToInt32(reader["TruckID"]),
                        Type = reader["Type"].ToString(),
                        Tonnage = Convert.ToInt32(reader["Tonnage"]),
                        State = Convert.ToByte(reader["State"]),
                        Number = reader["Number"].ToString(),
                        BuyDate = Convert.ToDateTime(reader["BuyDate"]),
                        FK_TeamID=Convert.ToInt32(reader["FK_TeamID"]),
                        TeamName = reader["TeamName"].ToString(),
                        CheckInTime = Convert.ToDateTime(reader["CheckInTime"]),
                        IsDelete = Convert.ToByte(reader["State"]),
                        AlterTime = Convert.ToDateTime(reader["AlterTime"]),
                        Length = reader["Length"].ToString()
                    };

                }

            }
            reader.Close();
            return truck;
        }
        #endregion

        #region 添加车辆
        /// <summary>
        /// 添加车辆
        /// </summary>
        /// <param name="truck">车辆</param>
        /// <returns>int类型</returns>
        public static int AddTruck(Truck truck)
        {
            string sql = @"INSERT INTO dbo.Truck(
  
      Number,
      BuyDate,
      Type,
      Length,
      Tonnage,
      FK_TeamID,
      State,
      Remark,
      CheckInTime,
      IsDelete,
      AlterTime
  )
  VALUES
  (
      @number,
      @buydate, 
      @type, 
      @length, 
      0,
      @teamId,
      @state,
      @remark,
      GETDATE(),
      0,
      GETDATE()
      )";
            SqlParameter[] parameters = {
            new SqlParameter("@number",truck.Number),
            new SqlParameter("@buydate",truck.BuyDate),
            new SqlParameter("@type",truck.Type),
            new SqlParameter("@length",truck.Length),
            new SqlParameter("@teamId",truck.FK_TeamID),
            new SqlParameter("@state",truck.State),
            new SqlParameter("@remark",truck.Remark),
            };
            return SqlHelper.ExecuteNonQuery(sql, parameters);

        }
        #endregion

        #region 删除车辆
        /// <summary>
        /// 删除车辆
        /// </summary>
        /// <param name="tid">通过车辆编号删除</param>
        /// <returns>int类型</returns>
        public static int DeleteTruck(int tid)
        {
            string sql = "delete from Truck where TruckID=@truckId";
            SqlParameter[] parameters = {
            new SqlParameter("@truckId",tid)
            };
            return SqlHelper.ExecuteNonQuery(sql, parameters);
        
        }
        #endregion

        #region 修改车辆
        /// <summary>
        /// 修改车辆
        /// </summary>
        /// <param name="truck">通过传递一个Truck对象类来传递数据</param>
        /// <returns>int类型</returns>
        public static int EditTruck(Truck truck)
        {
            string sql = "UPDATE dbo.Truck SET Number=@number,Type=@type,Length=@length,FK_TeamID=@teamId,State=@state,BuyDate=@buydate,Remark=@remark  WHERE TruckID=@truckId";
            SqlParameter[] parameters = {
            new SqlParameter("@number",truck.Number),
            new SqlParameter("@buydate",truck.BuyDate),
            new SqlParameter("@type",truck.Type),
            new SqlParameter("@length",truck.Length),
            new SqlParameter("@teamId",truck.FK_TeamID),
            new SqlParameter("@state",truck.State),
            new SqlParameter("@remark",truck.Remark),
            new SqlParameter("@truckId",truck.TruckID)
            };
            return SqlHelper.ExecuteNonQuery(sql, parameters);
        }
        #endregion

        #region 所属车队下拉框
        /// <summary>
        /// 通过查询TeamTruck里面的车队编号和车队名称来绑定下拉框
        /// </summary>
        /// <returns></returns>
        public static List<TruckTeam> GetTruckName()
        {
            string sql = "SELECT DISTINCT TeamID,TeamName FROM TruckTeam";
            SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            List<TruckTeam> teams = new List<TruckTeam>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    TruckTeam team = new TruckTeam();
                    team.TeamName = reader["TeamName"].ToString();
                    team.TeamID = Convert.ToInt32(reader["TeamID"]);
                    teams.Add(team);
                }
            }
            reader.Close();
            return teams;

        }
        #endregion

        #region 车辆类型下拉框
        /// <summary>
        /// 车辆类型下拉框
        /// </summary>
        /// <returns></returns>
        public static List<Truck> GetTruckType()
        {
            string sql = " SELECT DISTINCT Type FROM Truck";
            SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            List<Truck> teams = new List<Truck>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Truck team = new Truck();
                    team.Type = reader["Type"].ToString();       
                    teams.Add(team);
                }
            }
            reader.Close();
            return teams;

        }
        #endregion

        #region 统计数据总条数
        /// <summary>
        /// 统计总条数
        /// </summary>
        /// <returns>int类型</returns>
        public static int GetTruckCount()
        {
            string sql = "SELECT COUNT(*) FROM dbo.Truck,dbo.TruckTeam WHERE dbo.TruckTeam.TeamID=dbo.Truck.FK_TeamID";
            return  Convert.ToInt32(SqlHelper.ExecuteScalar(sql));

        }
        #endregion
        

      



    }
}
